﻿using DBUtil;
using Model.Permission;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;


namespace DAL.Permission.Manage
{
    public class SupplierUserDAL
    {
        /// <summary>
        /// 得到一个对象实体
        /// <param name="loginname">登录名</param>
        /// <param name="password">密码 注意该字段为解密后</param>
        /// </summary>
        public SupplierUserEntity GetModel(string loginname, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 [UserID],[SupplierID],[LoginName],[Password],[CreateTime],[Status],[LastLoginTime],[LastLoginIP] from ST_SupplierUser ");
            strSql.Append(" where LoginName=@LoginName and Password=@Password");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginName", loginname),
                    new SqlParameter("@Password",password)
			};

            SupplierUserEntity model = new SupplierUserEntity();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["UserID"] != null && ds.Tables[0].Rows[0]["UserID"].ToString() != "")
                {
                    model.UserID = int.Parse(ds.Tables[0].Rows[0]["UserID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SupplierID"] != null && ds.Tables[0].Rows[0]["SupplierID"].ToString() != "")
                {
                    model.SupplierID = int.Parse(ds.Tables[0].Rows[0]["SupplierID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LoginName"] != null && ds.Tables[0].Rows[0]["LoginName"].ToString() != "")
                {
                    model.LoginName = ds.Tables[0].Rows[0]["LoginName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Password"] != null && ds.Tables[0].Rows[0]["Password"].ToString() != "")
                {
                    model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CreateTime"] != null && ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = ds.Tables[0].Rows[0]["CreateTime"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Status"] != null && ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LastLoginTime"] != null && ds.Tables[0].Rows[0]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = ds.Tables[0].Rows[0]["LastLoginTime"].ToString();
                }
                if (ds.Tables[0].Rows[0]["LastLoginIP"] != null && ds.Tables[0].Rows[0]["LastLoginIP"].ToString() != "")
                {
                    model.LastLoginIP = ds.Tables[0].Rows[0]["LastLoginIP"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 更新某用户的密码
        /// </summary>
        /// <param name="uname"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool UpdatePwd(int uid, string pwd)
        {
            var sql = "UPDATE ST_SupplierUser SET [Password] = @U_PassWord WHERE UserID=@P_Id";
            SqlParameter[] parameters = {
					new SqlParameter("@P_Id", uid),
                    new SqlParameter("@U_PassWord", pwd)
			};
            return (DbHelperSQL.ExecuteSql(sql, parameters) > 0);
        }

        /// <summary>
        /// 更新登录时间和ip
        /// </summary>
        /// <param name="ip"></param>
        /// <param name="time"></param>
        /// <returns></returns>
        public bool UpdateIPAndLoginTime(int uid, string LastLoginIP, string LastLoginTime)
        {
            if (LastLoginIP == "::1")
            {
                LastLoginIP += "(本机)";
            }
            var sql = "UPDATE ST_SupplierUser SET [LastLoginTime] = @LastLoginTime,LastLoginIP=@LastLoginIP WHERE UserID=@P_Id";
            SqlParameter[] parameters = {
					new SqlParameter("@P_Id", uid),
                    new SqlParameter("@LastLoginIP", LastLoginIP),
                    new SqlParameter("@LastLoginTime", LastLoginTime)
			};
            return (DbHelperSQL.ExecuteSql(sql, parameters) > 0);
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public bool DisableUser(int uid)
        {
            var sql = "UPDATE ST_SupplierUser SET [Status] = 3 WHERE UserID=@pid";
            SqlParameter[] parameters = {
					new SqlParameter("@pid", SqlDbType.Int,4)
			};
            parameters[0].Value = uid;
            return (DbHelperSQL.ExecuteSql(sql, parameters) > 0);
        }

        /// <summary>
        /// 是否存在该记录
        /// <param name="LoginName">登录名</param>
        /// <param name="Password">密码</param>
        /// </summary>
        public int? Exists(string LoginName, string Password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 UserID from ST_SupplierUser");
            strSql.Append(" where [LoginName]=@LoginName and [Password]=@Password AND Status=1");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginName", LoginName),
                    new SqlParameter("@Password", Password)
                                        };
            var reader = DbHelperSQL.ExecuteReader(strSql.ToString(), parameters);
            if (reader.Read())
                return reader.GetInt32(0);
            return null;
        }

        /// <summary>
        /// 用户名是否存在
        /// </summary>
        /// <param name="LoginName">登录名</param>
        /// <returns></returns>
        public bool ExistsName(string LoginName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 UserID from ST_SupplierUser");
            strSql.Append(" where [LoginName]=@LoginName");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginName", LoginName)
                                        };
            var reader = DbHelperSQL.Exists(strSql.ToString(), parameters);
            return reader;
        }

        /// <summary>
        /// 是否存在当前用户名以外的其他用户名
        /// </summary>
        /// <param name="LoginName">登录名</param>
        /// <param name="UserID">密码</param>
        /// <returns></returns>
        public bool ExistsOtherUser(string LoginName, int UserID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 UserID from ST_SupplierUser");
            strSql.Append(" where [LoginName]=@LoginName and UserID<>@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginName", LoginName),
                    new SqlParameter("@UserID", UserID),
                                        };
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int UserID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ST_SupplierUser");
            strSql.Append(" where UserID=@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)
			};
            parameters[0].Value = UserID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SupplierUserEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ST_SupplierUser(");
            strSql.Append("SupplierID,LoginName,Password,CreateTime,Status,LastLoginTime,LastLoginIP)");
            strSql.Append(" values (");
            strSql.Append("@SupplierID,@LoginName,@Password,@CreateTime,@Status,@LastLoginTime,@LastLoginIP)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@SupplierID", SqlDbType.Int,4),
					new SqlParameter("@LoginName", SqlDbType.NVarChar,30),
					new SqlParameter("@Password", SqlDbType.NVarChar,32),
					new SqlParameter("@CreateTime", SqlDbType.NVarChar,50),
					new SqlParameter("@Status", SqlDbType.TinyInt,1),
					new SqlParameter("@LastLoginTime", SqlDbType.NVarChar,50),
					new SqlParameter("@LastLoginIP", SqlDbType.VarChar,15)};
            parameters[0].Value = model.SupplierID;
            parameters[1].Value = model.LoginName;
            parameters[2].Value = model.Password;
            parameters[3].Value = model.CreateTime;
            parameters[4].Value = model.Status;
            parameters[5].Value = model.LastLoginTime;
            parameters[6].Value = model.LastLoginIP;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(SupplierUserEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ST_SupplierUser set ");
            strSql.Append("SupplierID=@SupplierID,");
            strSql.Append("LoginName=@LoginName,");
            strSql.Append("Status=@Status,");
            strSql.Append(" where UserID=@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@SupplierID", model.SupplierID),
					new SqlParameter("@LoginName", model.LoginName),
					new SqlParameter("@Status", model.Status),
					new SqlParameter("@UserID", model.UserID)};

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int UserID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ST_SupplierUser ");
            strSql.Append(" where UserID=@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)
			};
            parameters[0].Value = UserID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string UserIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ST_SupplierUser ");
            strSql.Append(" where UserID in (" + UserIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public SupplierUserEntity GetModel(int UserID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 UserID,SupplierID,LoginName,Password,CreateTime,Status,LastLoginTime,LastLoginIP from ST_SupplierUser ");
            strSql.Append(" where UserID=@UserID");
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)
			};
            parameters[0].Value = UserID;

            SupplierUserEntity model = new SupplierUserEntity();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["UserID"] != null && ds.Tables[0].Rows[0]["UserID"].ToString() != "")
                {
                    model.UserID = int.Parse(ds.Tables[0].Rows[0]["UserID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SupplierID"] != null && ds.Tables[0].Rows[0]["SupplierID"].ToString() != "")
                {
                    model.SupplierID = int.Parse(ds.Tables[0].Rows[0]["SupplierID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LoginName"] != null && ds.Tables[0].Rows[0]["LoginName"].ToString() != "")
                {
                    model.LoginName = ds.Tables[0].Rows[0]["LoginName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Password"] != null && ds.Tables[0].Rows[0]["Password"].ToString() != "")
                {
                    model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CreateTime"] != null && ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = ds.Tables[0].Rows[0]["CreateTime"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Status"] != null && ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LastLoginTime"] != null && ds.Tables[0].Rows[0]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = ds.Tables[0].Rows[0]["LastLoginTime"].ToString();
                }
                if (ds.Tables[0].Rows[0]["LastLoginIP"] != null && ds.Tables[0].Rows[0]["LastLoginIP"].ToString() != "")
                {
                    model.LastLoginIP = ds.Tables[0].Rows[0]["LastLoginIP"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

    }
}
